#!C:\perl\bin
use strict;
use warnings;
use lib "/perl/lib";
use Spreadsheet::ParseExcel;
use DBI;
use DateTime::Format::Pg;
use utf8;
use Encode;

my $fichier=$ARGV[0];
my $exp=encode_utf8($ARGV[1]);
$exp=~s/_/\ /;
my $parser=Spreadsheet::ParseExcel->new();
my $workbook=$parser->parse("$fichier");
if (!defined $workbook){
    die $parser->error(),".\n";
}

my $worksheet;
my %result;
my %infos;
my $microcebe="";
my $micro;

foreach($workbook->worksheets()){
    $worksheet=$_;
}
my ($row_min,$row_max) = $worksheet->row_range();
my ($col_min, $col_max) = $worksheet->col_range();

for my $row (3 .. $row_max){ #parcours lignes
	my $naissance;
	my $date_test;
	my $result_t;
	
	$micro=$microcebe;
    for my $col ($col_min .. $col_max){ #parcours colonnes
		if ($col eq 0) { #recup num microcebe
			my $cont=$worksheet->get_cell($row,$col);
			$microcebe=$cont->value();
		}
		if($microcebe){
			if ($col eq 1) { #recup date naissance
				my $cont=$worksheet->get_cell($row,$col);
				$naissance=$cont->value();
			}
			if ($col eq 2) { #recup date test
				my $cont=$worksheet->get_cell($row,$col);
				$date_test=$cont->value();	
			}
			if ($col eq 10) { #recup % freezing
				my $cont=$worksheet->get_cell($row,$col);
				$result_t=$cont->value();
			}
		}
    }
	
	if($micro ne $microcebe){ #si le micro précédent est différent du courant => nouveau microcebe
		if($micro ne ""){
			$result{$micro}={%infos};
		}
		
		%infos=();
		if($naissance){
			$infos{'naissance'}=$naissance;
		}
	}
	if($date_test){
		$infos{$date_test}=$result_t; 
	}
}

#~ my $util;
#~ foreach $util (sort keys %result) {
	#~ print "$util\n";
    #~ foreach (keys %{$result{$util}}) {
		#~ print ("$_ : $result{$util}{$_} \n");
    #~ }
#~ }




# ********************* INSERTION DONNEES DANS LA BASE **************************** 


# connection a sql
my $dbh=DBI->connect("DBI:Pg:dbname='mmdn';host=localhost","postgres","mmdn",{'RaiseError'=>1});
# execute INSERT query

# ************************************* Création type_test ************************************* #

my $unite_mesure='Pourcentage freezing';
my $id_type_test;
$id_type_test=$dbh->selectrow_array("SELECT id_type_test FROM type_test WHERE label='Test comportemental' AND description like '%Emotivité%' AND unite_mesure='$unite_mesure'");

if(not $id_type_test){
	$dbh->do("INSERT INTO Type_test(label, description,unite_mesure) VALUES ('Test comportemental', 'Emotivité', '$unite_mesure')"); 
	$id_type_test=$dbh->selectrow_array("SELECT id_type_test FROM type_test WHERE label='Test comportemental' AND description like 'Emotivité' AND unite_mesure='$unite_mesure'");
}



foreach my $code (keys %result) { #$code =code_animal
	#recuperation du code_animal et de sa date de naissance =>creation de l'animal
	my $date=$result{$code}{'naissance'};
	my $id_anim;
	my @tab;

	# ************************************* Création animal ************************************* #
	
	### verification de l'existence de l'animal dans la base
	my $temp=$dbh->prepare("SELECT distinct(id_animal,date_naissance) FROM Animal WHERE code_animal=(E'$code')");
	$temp->execute();
	while (my @val=$temp->fetchrow_array()){
		push(@tab,$val[0]);
	}
	$temp->finish();
	
	my $size=@tab;
	if($size eq 0){ #aucun animal n'existe avec ce code
		$dbh->do("INSERT INTO Animal(code_animal,date_naissance) VALUES ('$code','$date')");
		$id_anim=$dbh->selectrow_array("SELECT id_animal FROM Animal WHERE code_animal=(E'$code') and date_naissance=(E'$date')");
	}
	elsif($size eq 1){ #un seul animal existe avec ce code, on vérifie que les dates de naissance correspondent
		foreach my $i(@tab){
			my @temp=split(',',$i);
			my $id=$temp[0];
			$id=~s/\(//; #id trouvé
			my $nai=$temp[1];
			$nai=~s/\)//; #date de naissance trouvée
			if($nai ne ''){ #si l'animal existant a une date de naissance
				if($nai eq $date){ #on vérifie que les dates coïncident
					$id_anim=$id;
					last;
				}
				else{ #si elles ne coïncident pas, on créé un nouvel animal
					$dbh->do("INSERT INTO Animal(code_animal,date_naissance) VALUES ('$code','$date')");
					$id_anim=$dbh->selectrow_array("SELECT id_animal FROM Animal WHERE code_animal=(E'$code') and date_naissance=(E'$date')");
					last;
				}
			}
			else{ #si l'animal existant n'a pas de date de naissance, on l'update
				$dbh->do("UPDATE Animal SET date_naissance=(E'$date') WHERE id_animal=$id");
				$id_anim=$id;
			}
		}
	}
	elsif($size>1){ #s'il existe plusieurs animaux avec ce code
		foreach my $i(@tab){
			my @temp=split(',',$i);
			my $id=$temp[0];
			$id=~s/\(//; #id trouvé
			my $nai=$temp[1];
			$nai=~s/\)//; #date de naissance trouvée
			if($nai ne ' '){ #si l'animal existant a une date de naissance
				if($nai eq $date){ #on vérifie que les dates coïncident
					$id_anim=$id;
					last;
				}
			}
			elsif($nai eq ' '){ #si l'animal trouvé n'a pas de date de naissance, on lui en met une
				$dbh->do("UPDATE Animal SET date_naissance=(E'$date') WHERE id_animal=$id");
				$id_anim=$id;
				last;
			}
		}
		if(not $id_anim){ #si aucune date ne correspond, on créé un nouvel animal
			$dbh->do("INSERT INTO Animal(code_animal,date_naissance) VALUES ('$code','$date')");
			$id_anim=$dbh->selectrow_array("SELECT id_animal FROM Animal WHERE code_animal=(E'$code') and date_naissance=(E'$date')");
		}
	}
	
	foreach (keys %{$result{$code}}) {
		if ($_ ne 'naissance'){
			my $datet=$_; #date du test
			my $value=$result{$code}{$_};
			my @val2=split('%',$value);
			my $result2=$val2[0];
			my $id_test2;
			my $id_res2;
			
			# ************************************* Création test ************************************* #
			
			### verification que le test n'existe pas deja ###
			$id_test2=$dbh->selectrow_array("SELECT id_test FROM Test WHERE experimentateur='$exp' AND id_type_test=$id_type_test AND date_test=(E'$datet')");
					
			### ajout du test ###
			if(not $id_test2){
				$dbh->do("INSERT INTO Test(date_test, experimentateur, id_type_test) VALUES ('$datet', '$exp', $id_type_test)");
				$id_test2=$dbh->selectrow_array("SELECT id_test FROM Test WHERE experimentateur='$exp' AND id_type_test=$id_type_test AND date_test=(E'$datet')");
			}

			# ************************************* Création resultat_test ************************************* #
			
			$id_res2=$dbh->selectrow_array("SELECT id_res from resultat_test WHERE id_test=$id_test2 and id_animal=$id_anim");
			
			if(not $id_res2){
				$dbh->do("INSERT INTO Resultat_test(id_test, id_animal) VALUES($id_test2, $id_anim)");
				$id_res2=$dbh->selectrow_array("SELECT id_res from resultat_test WHERE id_test=$id_test2 and id_animal=$id_anim");
			}

			# ************************************* Création resultat_test_simple ************************************* #
			
			my $resultat=$dbh->selectrow_array("SELECT id_res FROM resultat_test_simple WHERE id_res=$id_res2");
			
			if(not $resultat){
				$dbh->do("INSERT INTO Resultat_test_simple(id_res, mesure) VALUES($id_res2, $result2)");			
			}
		}
	}
}

$dbh->disconnect();
